﻿using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Configuration;

namespace PCCardN
{
    /// <summary>
    /// 类概要:Sql数据库数据访问层通用公共静态方法
    /// </summary>
    public class SqlHelper
    {
        /// <summary>
        /// 从配置文件中读取数据库连接字符串
        /// </summary>
        public SqlDatabase db;

        //private string _strSqlConn;
        //public string strSqlConn
        //{
        //    get
        //    {
        //        return _strSqlConn;
        //    }
        //    set
        //    {
        //        _strSqlConn = value;
        //        if (!string.IsNullOrEmpty(_strSqlConn))
        //        {
        //            db = new SqlDatabase(_strSqlConn);
        //        }
        //    }
        //}

        public string GetConString()
        {
            return Properties.Settings.Default.ConnString;
        }

        #region 公共静态方法

        /// <summary>
        /// 填充DataTable
        /// </summary>
        /// <param name="spname"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        public DataTable FillDataTable(CommandType cmdType, string spname, params SqlParameter[] sqlParams)
        {
            DbCommand cmd = ConvertToDbCommand(cmdType, spname);
            if (sqlParams != null && sqlParams.Length > 0)
            {
                for (int i = 0; i < sqlParams.Length; i++)
                {
                    db.AddInParameter(cmd, sqlParams[i].ParameterName, sqlParams[i].DbType, sqlParams[i].Value);
                }
            }
            return db.ExecuteDataSet(cmd).Tables[0];
        }
        /// <summary>
        /// 填充DataTable
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="spname"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        public DataTable FillPageDataTable(CommandType cmdType, string spname, params SqlParameter[] sqlParams)
        {
            DbCommand cmd = ConvertToDbCommand(cmdType, spname);
            if (sqlParams != null && sqlParams.Length > 0)
            {
                for (int i = 0; i < sqlParams.Length; i++)
                {
                    if (sqlParams[i].Direction != ParameterDirection.Output)
                    {
                        db.AddInParameter(cmd, sqlParams[i].ParameterName, sqlParams[i].DbType, sqlParams[i].Value);
                    }
                    else
                    {
                        db.AddOutParameter(cmd, sqlParams[i].ParameterName, sqlParams[i].DbType, sqlParams[i].Size);
                    }
                }
            }
            DataTable table = db.ExecuteDataSet(cmd).Tables[0];
            for (int j = 0; j < sqlParams.Length; j++)
            {
                if (sqlParams[j].Direction == ParameterDirection.Output)
                {
                    sqlParams[j].Value = db.GetParameterValue(cmd, sqlParams[j].ParameterName);
                }
            }
            return table;
        }
        /// <summary>
        /// 填充DataReader
        /// </summary>
        /// <param name="spname"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        public SqlDataReader ExecuteReader(CommandType cmdType, string spname, params SqlParameter[] sqlParams)
        {
            DbCommand cmd = ConvertToDbCommand(cmdType, spname);
            if (sqlParams != null && sqlParams.Length > 0)
            {
                for (int i = 0; i < sqlParams.Length; i++)
                {
                    db.AddInParameter(cmd, sqlParams[i].ParameterName, sqlParams[i].DbType, sqlParams[i].Value);
                }
            }
            return (SqlDataReader)db.ExecuteReader(cmd);
        }

        /// <summary>
        /// 执行SQL语句，无返回记录
        /// </summary>
        /// <param name="cmdText">要执行的SQL语句或存储过程名</param>
        /// <param name="sqlParams">SQL参数集合</param>
        /// <returns></returns>
        public int ExecuteNonQuery(CommandType cmdType, string spname, params SqlParameter[] sqlParams)
        {
            DbCommand cmd = ConvertToDbCommand(cmdType, spname);
            if (sqlParams != null && sqlParams.Length > 0)
            {
                for (int i = 0; i < sqlParams.Length; i++)
                {
                    db.AddInParameter(cmd, sqlParams[i].ParameterName, sqlParams[i].DbType, sqlParams[i].Value);
                }
            }
            return db.ExecuteNonQuery(cmd);
        }

        /// <summary>
        /// 执行存储过程，无返回记录
        /// </summary>
        /// <param name="spname">存储过程名称</param>
        /// <param name="sqlParams">存储过程参数集合</param>
        /// <returns>受影响的记录行数</returns>
        public int ExecuteNonQueryproc(string spname, params SqlParameter[] sqlParams)
        {
            return ExecuteNonQuery(CommandType.StoredProcedure, spname, sqlParams);
        }

        /// <summary>
        /// 执行SQL语句或存储过程，返回第一行第一列的数据
        /// </summary>
        /// <param name="spname"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        public object ExecuteScalar(string spname, params SqlParameter[] sqlParams)
        {
            CommandType cmdType = CommandType.StoredProcedure;
            DbCommand cmd = ConvertToDbCommand(cmdType, spname);
            if (sqlParams != null && sqlParams.Length > 0)
            {
                for (int i = 0; i < sqlParams.Length; i++)
                {
                    db.AddInParameter(cmd, sqlParams[i].ParameterName, sqlParams[i].DbType, sqlParams[i].Value);
                }
            }
            return db.ExecuteScalar(cmd);
        }


        /// <summary>
        /// 执行SQL语句或存储过程，返回第一行第一列的数据
        /// </summary>
        /// <param name="spname"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        public object ExecuteScalar(CommandType cmdType, string spname, params SqlParameter[] sqlParams)
        {
            DbCommand cmd = ConvertToDbCommand(cmdType, spname);
            if (sqlParams != null && sqlParams.Length > 0)
            {
                for (int i = 0; i < sqlParams.Length; i++)
                {
                    db.AddInParameter(cmd, sqlParams[i].ParameterName, sqlParams[i].DbType, sqlParams[i].Value);
                }
            }
            return db.ExecuteScalar(cmd);
        }

        /// <summary>
        /// 向数据库中插入或更新数据时，设置存储过程参数
        /// </summary>
        /// <param name="pName">存储过程参数名</param>
        /// <param name="pValue">参数值</param>
        /// <returns>返回SqlParameter</returns>
        public SqlParameter SetParam(string pName, object pValue)
        {
            //如果pValue为null，则直接返回 2004-12-6
            if (pValue == null)
                return new SqlParameter(pName, pValue);
            switch (Type.GetTypeCode(pValue.GetType()))
            {
                case TypeCode.String:
                    string tempStr = (string)pValue;
                    if (tempStr.Equals(null) || tempStr.Trim().Length.Equals(0))
                    {
                        return new SqlParameter(pName, DBNull.Value);
                    }
                    return new SqlParameter(pName, tempStr);
                case TypeCode.DateTime:
                    DateTime tempdt = (DateTime)pValue;
                    if (tempdt.Equals(DateTime.MinValue))
                    {
                        return new SqlParameter(pName, DBNull.Value);
                    }
                    return new SqlParameter(pName, tempdt);
                default:
                    return new SqlParameter(pName, pValue);
            }
        }

        /// <summary>
        /// 获取SqlDataReader中指定字符串字段的值
        /// </summary>
        /// <param name="reader">包含数据的SqlDataReader</param>
        /// <param name="fieldName">字段名</param>
        /// <returns>字段值，若字段为空则返回空字符串</returns>
        public string GetStringFieldValue(SqlDataReader reader, string fieldName)
        {
            if (Convert.IsDBNull(reader[fieldName]))
            {
                return string.Empty;
            }
            return reader[fieldName].ToString();
        }

        /// <summary>
        /// 获取SqlDataReader中指定Int字段的值，若该字段为空则返回指定的替换值
        /// </summary>
        /// <param name="reader">包含数据的SqlDataReader</param>
        /// <param name="fieldName">字段名</param>
        /// <param name="replaceValue">要替换值为空的字段的值</param>
        /// <returns>字段值，若该字段为空则返回replaceValue</returns>
        public int GetIntFieldValue(SqlDataReader reader, string fieldName, int replaceValue)
        {
            if (Convert.IsDBNull(reader[fieldName]))
            {
                return replaceValue;
            }
            object obj = reader[fieldName];
            return Convert.ToInt32(reader[fieldName]);
        }

        /// <summary>
        /// 获取SqlDataReader中指定Int字段的值
        /// </summary>
        /// <param name="reader">包含数据的SqlDataReader</param>
        /// <param name="fieldName">字段名</param>
        /// <returns>字段值，若该字段为空则返回0</returns>
        public int GetIntFieldValue(SqlDataReader reader, string fieldName)
        {
            return GetIntFieldValue(reader, fieldName, 0);
        }


        /// <summary>
        /// 获取SqlDataReader中指定byte字段（SQL Server中为tinyint类型）的值
        /// </summary>
        /// <param name="reader">包含数据的SqlDataReader</param>
        /// <param name="fieldName">要取值的字段名</param>
        /// <returns>返回byte类型结果</returns>
        public byte GetByteFieldValue(SqlDataReader reader, string fieldName)
        {
            if (Convert.IsDBNull(reader[fieldName]))
                return 0;
            return (byte)reader[fieldName];
        }

        /// <summary>
        /// 获取SqlDataReader中指定decimal字段的值，若该字段为空则返回指定的替换值
        /// </summary>
        /// <param name="reader">包含数据的SqlDataReader</param>
        /// <param name="fieldName">要取值的字段名</param>
        /// <param name="replaceValue">要替换值为空的字段的值</param>
        /// <returns>返回double类型结果</returns>
        public double GetDoubleFieldValue(SqlDataReader reader, string fieldName, double replaceValue)
        {
            if (Convert.IsDBNull(reader[fieldName]))
                return replaceValue;
            return (double)reader[fieldName];
        }

        /// <summary>
        /// 获取SqlDataReader中指定Int字段的值，若该字段为空则返回0
        /// </summary>
        /// <param name="reader">包含数据的SqlDataReader</param>
        /// <param name="fieldName">要取值的字段名</param>
        /// <returns>返回double类型结果</returns>
        public double GetDoubleFieldValue(SqlDataReader reader, string fieldName)
        {
            return GetDoubleFieldValue(reader, fieldName, 0);
        }
        /// <summary>
        /// 获取SqlDataReader中指定Int字段的值，若该字段为空则返回0
        /// </summary>
        /// <param name="reader">包含数据的SqlDataReader</param>
        /// <param name="fieldName">要取值的字段名</param>
        /// <returns>返回decimal类型结果</returns>
        public decimal GetDecimalFieldValue(SqlDataReader reader, string fieldName)
        {
            if (Convert.IsDBNull(reader[fieldName]))
                return decimal.Parse("0.00");
            return decimal.Parse(reader[fieldName].ToString());
        }
        /// <summary>
        /// 获取SqlDataReader中指定float字段的值，若该字段为空则返回0
        /// </summary>
        /// <param name="reader">包含数据的SqlDataReader</param>
        /// <param name="fieldName">要取值的字段名</param>
        /// <returns>返回double类型结果</returns>
        public float GetFloatFieldValue(SqlDataReader reader, string fieldName)
        {
            return GetFloatFieldValue(reader, fieldName, 0);
        }

        /// <summary>
        /// 获取SqlDataReader中指定decimal字段的值，若该字段为空则返回指定的替换值
        /// </summary>
        /// <param name="reader">包含数据的SqlDataReader</param>
        /// <param name="fieldName">要取值的字段名</param>
        /// <param name="replaceValue">要替换值为空的字段的值</param>
        /// <returns>返回double类型结果</returns>
        public float GetFloatFieldValue(SqlDataReader reader, string fieldName, float replaceValue)
        {
            if (Convert.IsDBNull(reader[fieldName]))
                return replaceValue;
            return float.Parse(reader[fieldName].ToString());
        }

        /// <summary>
        /// 获取SqlDataReader中指定Int字段的值，若该字段为空则返回DateTime的最小值
        /// </summary>
        /// <param name="reader">包含数据的SqlDataReader</param>
        /// <param name="fieldName">要取值的字段名</param>
        /// <returns>返回DateTime类型结果</returns>
        public DateTime GetDateTimeFieldValue(SqlDataReader reader, string fieldName)
        {
            if (Convert.IsDBNull(reader[fieldName]))
                return DateTime.MinValue;
            return (DateTime)reader[fieldName];
        }
        public DataTable GetTable()
        {
            return null;
        }

        DbCommand ConvertToDbCommand(CommandType cmdtype, string cmdText)
        {
            DbCommand cmd = null;
            if (cmdtype == CommandType.StoredProcedure)
            {
                cmd = db.GetStoredProcCommand(cmdText);
            }
            else if (cmdtype == CommandType.Text)
            {
                cmd = db.GetSqlStringCommand(cmdText);
            }
            cmd.CommandTimeout = 0;
            return cmd;
        }


        /// <summary>
        /// 执行存储过程，返回输出参数的值
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="OutParameter">输出参数名称</param>
        /// <returns></returns>
        public object RunProcedure(string storedProcName, IDataParameter[] InParameters, SqlParameter OutParameter)
        {
            //P_USER_INFO_ADD_1
            DbCommand dbCommand = db.GetStoredProcCommand(storedProcName);
            dbCommand.CommandTimeout = 0;
            BuildDBParameter(db, dbCommand, (SqlParameter[])InParameters);
            db.AddOutParameter(dbCommand, OutParameter.ParameterName, OutParameter.DbType, OutParameter.Size);
            db.ExecuteNonQuery(dbCommand);
            return db.GetParameterValue(dbCommand, OutParameter.ParameterName);  //得到输出参数的值
        }


        /// <summary>
        /// 执行存储过程，返回影响的行数
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="rowsAffected">影响的行数</param>
        /// <returns></returns>
        public object RunProcedure(string storedProcName, IDataParameter[] InParameters, int rowsAffected)
        {
            DbCommand dbCommand = db.GetStoredProcCommand(storedProcName);
            BuildDBParameter(db, dbCommand, (SqlParameter[])InParameters);
            return rowsAffected = db.ExecuteNonQuery(dbCommand);
        }

        /// <summary>
        /// 加载参数
        /// </summary>
        public void BuildDBParameter(Database db, DbCommand dbCommand, params SqlParameter[] cmdParms)
        {
            foreach (SqlParameter sp in cmdParms)
            {
                if (sp.Direction != ParameterDirection.Output)
                {
                    db.AddInParameter(dbCommand, sp.ParameterName, sp.DbType, sp.Value);
                }
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>
        public int ExecuteSqlTran(ArrayList ProcedureList)
        {
            using (DbConnection dbconn = db.CreateConnection())
            {
                dbconn.Open();
                DbTransaction dbtran = dbconn.BeginTransaction();
                try
                {
                    //执行语句
                    for (int n = 0; n < ProcedureList.Count; n++)
                    {
                        string strsql = ProcedureList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            DbCommand dbCommand = db.GetSqlStringCommand(strsql);
                            db.ExecuteNonQuery(dbCommand);
                        }
                    }
                    dbtran.Commit();
                    return 1;
                }
                catch
                {
                    dbtran.Rollback();

                }
                finally
                {
                    dbconn.Close();
                }
                return 0;
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="_list">sql集合</param>
        /// <returns></returns>
        public int ExecuteSqlTran(List<string> _list)
        {
            using (DbConnection dbconn = db.CreateConnection())
            {
                dbconn.Open();
                DbTransaction dbtran = dbconn.BeginTransaction();
                try
                {
                    //执行语句
                    for (int n = 0; n < _list.Count; n++)
                    {
                        string strsql = _list[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            DbCommand dbCommand = db.GetSqlStringCommand(strsql);
                            db.ExecuteNonQuery(dbCommand);
                        }
                    }
                    dbtran.Commit();
                    return 1;
                }
                catch
                {
                    dbtran.Rollback();

                }
                finally
                {
                    dbconn.Close();
                }
                return 0;
            }
        }
        #endregion 静态私有方法 结束

        /// <summary>
        /// 两张表联合查询
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable GetQueryResult(string sql)
        {
            DataTable tbl = new DataTable();
            SqlConnection conn = new SqlConnection(GetConString());
            try
            {

                using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conn))
                {
                    adapter.Fill(tbl);
                }
            }
            catch
            {

            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return tbl;
        }

        /// <summary>
        /// 执行一条SQL的select语句，用返回的结果填充DataTable
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <param name="oParams">SQL语句中的参数</param>
        /// <returns>已填充数据的DataTable</returns>		
        public DataTable FillSqlDataTable(string sql, params SqlParameter[] oParams)
        {
            SqlConnection conn = new SqlConnection(GetConString());
            using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conn))
            {
                if (oParams != null && oParams.Length > 0)
                {
                    foreach (SqlParameter prm in oParams)
                    {
                        adapter.SelectCommand.Parameters.Add(prm);
                    }
                }
                DataTable table = new DataTable();
                adapter.Fill(table);
                return table;
            }
        }
        /// 执行存储工程
        /// <summary>
        /// 填充DataTable
        /// </summary>
        /// <param name="spname"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        public DataTable FillDataTable(string spname, params SqlParameter[] sqlParams)
        {
            SqlConnection conn = new SqlConnection(GetConString());
            using (SqlDataAdapter adapter = new SqlDataAdapter(spname, conn))
            {
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                adapter.SelectCommand.CommandTimeout = 0;
                if (sqlParams != null && sqlParams.Length > 0)
                {
                    for (int i = 0; i < sqlParams.Length; i++)
                    {
                        adapter.SelectCommand.Parameters.Add(sqlParams[i]);
                    }
                }
                DataTable table = new DataTable();
                adapter.Fill(table);
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                return table;
            }
        }

        /// <summary>
        /// 执行SQL存储过程，返回第一行第一列的数据
        /// </summary>
        /// <param name="spname"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        public object ExecuteScalars(string spname, params SqlParameter[] sqlParams)
        {
            SqlConnection conn = new SqlConnection(GetConString());
            using (SqlCommand cmd = new SqlCommand(spname, conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 0;
                if (sqlParams != null && sqlParams.Length > 0)
                {
                    for (int i = 0; i < sqlParams.Length; i++)
                    {
                        cmd.Parameters.Add(sqlParams[i]);
                    }
                }
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                object obj = cmd.ExecuteScalar();
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                return obj;
            }
        }
        /// <summary>
        /// 数据库返回最后一次插入自动递增字段的IDENTITY.
        /// </summary>
        public int GetLastIdentity(SqlCommand cmd, string sql)
        {
            object obj = NonQueryAndGetLastIdentity(cmd, sql);
            string temp = obj.ToString();
            return obj != null ? int.Parse(obj.ToString()) : -1;
        }

        public object NonQueryAndGetLastIdentity(SqlCommand sqlcmd, string sql)
        {
            sqlcmd.Connection = new SqlConnection(GetConString());
            try
            {
                if (sqlcmd.Connection.State == ConnectionState.Closed)
                {
                    sqlcmd.Connection.Open();
                }
                if (sqlcmd.Connection != null)
                {
                    int backFlag = sqlcmd.ExecuteNonQuery();
                    if (backFlag > 0)
                    {
                        return ExecuteScalarOne(sql, sqlcmd);
                    }
                    else
                    {
                        return null;
                    }
                }

            }
            catch (Exception ex)
            {
                return 0;
            }
            finally
            {
                if (sqlcmd.Connection.State == ConnectionState.Open)
                    sqlcmd.Connection.Close();
            }
            return 0;
        }
        public int NonQuery(string sql)
        {
            SqlConnection conn = new SqlConnection(GetConString());
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;
            try
            {
                conn.Open();
                int flag = cmd.ExecuteNonQuery();
                conn.Close();
                return flag;
            }
            catch (Exception ex)
            {

                return -1;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }
        //返回第一行第一列的object值
        public object ExecuteScalarOne(string sql, SqlCommand cmd)
        {
            if (!string.IsNullOrEmpty(sql))
            {
                cmd.CommandText = sql;
                try
                {
                    object flag = cmd.ExecuteScalar();
                    string ddd = flag.ToString();
                    cmd.Connection.Close();
                    return flag;
                }
                catch (Exception ex)
                {

                    return null;
                }
                finally
                {
                    if (cmd.Connection.State == ConnectionState.Open)
                        cmd.Connection.Close();
                }
            }
            return null;
        }

    }
}
